<?php
/**
 * DynaGrid - The PHP datagrid
 *
 * @package    DynaGrid
 * @author     $Author: keesschepers $
 * @version    $Id: MysqlQueryParser.php 11 2011-02-11 23:20:17Z keesschepers $
 */

include_once 'DataSource/Adapter/Abstract.php';

/**
 * With this adapter it's possible to throw a (mysql) query thru DynaGrid
 * and DynaGrid takes care of the quering part and modification of the query
 * for consuming data in chunks and set the order by.
 * 
 * USE THIS ADAPTER WITH CARE!!
 * Because of the hardness to split a logically a SQL query it could be
 * that with a complex SQL query the adapter would get in a conflict and the data
 * displayed is corrupted or isn't displayed at all.
 *
 */
class DynaGrid_DataSource_Adapter_MysqliQueryParser extends DynaGrid_DataSource_Adapter_Abstract implements DynaGrid_DataSource_Adapter_Interface {
	const ARRAY_SQL = 0;
	/**
	 * MySQLi resource to the database.
	 * 
	 * @var type Mysqli
	 */
	private $connection;
	/**
	 *
	 * The internal pointer where the array needs to start.
	 *
	 * @var integer
	 */
	private $offset = 0;
	/**
	 * Limiting of the result is not done immediatly because otherwise totalrows
	 * count isn't correct. For this reason we save this value internal and the self::getData
	 * function limits the result.
	 * 
	 * @var integer 
	 */
	private $limit = 0;
	private $totalRows = 0;
	
	/**
	 * The SQL query string (select statement)
	 * 
	 * @var string 
	 */
	private $query;
	/**
	 *
	 * @var Mysqli_Result
	 */
	private $result;
	
  public function getTotalRows() {
		return $this->totalRows;
	}
	/**
	 * Done a little tricky but it works..
	 *
	 * @param integer $offset
	 */
	public function setOffset($offset) {
		$this->offset = $offset;
	}
	/**
	 *
	 * Sets the wanted limit to the selectquery
	 *
	 * @param integer $limit
	 */
	public function setLimit($limit) {		
		$this->limit = $limit;		
	}
	/**
	 * Return an array of field names
	 *
	 * @return array
	 */
	public function getFields() {		
		$fetchedFields = $this->result->fetch_fields();		
		$fields = array();
		
		foreach ($fetchedFields as $field) {
			$fields[] = $field->name;
		}
		
		return $fields;
	}
	/**
	 * Return the tabel data. Make a copy of the internal data because
	 * otherwise totalrows, etc won't work.
	 *
	 * @todo add CALC_FOUND_ROWS after SELECT part.
	 * @return array
	 */
	public function  getData() {
		//set the limit
		if($this->limit > 0 || $this->offset > 0) {
			$pos = strripos($this->query,'LIMIT');
			if($pos === false) $pos = strlen ($this->query);
			
			$this->query = substr_replace($this->query, ' LIMIT '. $this->limit, $pos);
			
			if($this->offset > 0 && $this->limit > 0) {
				$this->query .= ' OFFSET '.(int)$this->offset;
			}
		}
		
		
		//add CALC_FOUND_ROWS
		$this->query = preg_replace('/^(SELECT)(.*)/', '\\1 SQL_CALC_FOUND_ROWS \\2', $this->query);
		
		echo $this->query;				
		
		$this->result = $this->connection->query($this->query);
		
		$data = array();
		
		while($row = $this->result->fetch_array(MYSQLI_ASSOC)) {
			$data[] = $row;
		}
		
		$result = $this->connection->query('SELECT FOUND_ROWS()');
		$row = $result->fetch_row();
		
		$this->totalRows = $row[0];
		
		return $data;
	}
	
	/**
	 *
	 * If order by is set, reset the order by clause of the original query.
	 * 
	 * @todo make a difference between descending and ascending
	 * @param array $properties Array of properties to sort on
	 */
	public function setOrderBy(array $properties) {
		$orderby = ' ORDER BY ';
		foreach($properties as $orderbyClause) {
			$orderby .= $orderbyClause['field'] . ' ' . $orderbyClause['direction'] . ', ';
		}
		
		$orderby = substr($orderby,0,strlen($orderby) - 2);
		
		switch(true) {
			case ($pos = strripos($this->query,'ORDER BY')) !== false :
				/**
				 * Extract order by clause
				 * 
				 * Special thanks to Rick van Hattem for the regex.
				 */
				$pattern = '/(order by .*?)(| limit [0-9,]+\s*| limit [0-9]+ offset [0-9]+\s*)$/i';
				$this->query = preg_replace($pattern, $orderby . ' \\2', $this->query, 1);				
				break;
			case ($pos = strripos($this->query, 'LIMIT')) !== false :
				$this->query = substr_replace($this->query, $orderby, $pos);
				break;
			default :
				/**
				 * There is no limit by in the query and no order by so paste the order 
				 * by at the end of the query.
				 */
				$this->query .= ' ' . $orderby;
				break;
		}	
	}
	
	/**
	 *
	 * Only one parameter is taken, as first parameter a SQL query is given to the dynagrid.
	 * 
	 * @param array $arguments
	 * @return parent::setArguments 
	 */
	public function setArguments($arguments = array()) {
		if(stripos($arguments[self::ARRAY_SQL], 'select') === false) {
			include_once 'DynaGrid/Exception.php';
			throw new DynaGrid_Exception('Unable to build grid on a non-select statement.');
		}
		
		$this->query = $arguments[0];
		
		return parent::setArguments($arguments);
	}
	
	public function __construct($host, $username, $password, $database) {
		$this->connection = new mysqli($host, $username, $password, $database);
	}
}